<?php
/**************************************************************************
	ESAM (EVE to SMF API Module)
	Copyright (C) 2009  Clinton Weiss

	This program is free software: you can redistribute it and/or modify
	it under the terms of the GNU General Public License as published by
	the Free Software Foundation, either version 3 of the License, or
	(at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program.  If not, see <http://www.gnu.org/licenses/>.
**************************************************************************/

if (!defined('SMF'))
{
    include "../SSI.php";
}


/**
 * class for executing queries
 */
class EsamQuery
{
// API
    public $Q_EVE_API		= "SELECT * FROM {db_prefix}esam_api WHERE ID_MEMBER = {member_id}";
    public $Q_EVE_API_LIST	= "SELECT * FROM {db_prefix}esam_api ORDER BY ID_MEMBER";
    public $Q_EVE_API_SEARCH	= "SELECT * FROM {db_prefix}esam_api WHERE USER_ID = '{user_id}' AND API_KEY = '{api_key}'";
    public $D_EVE_API_DELETE	= "DELETE FROM {db_prefix}esam_api	WHERE ID_MEMBER = {member_id}";
    public $D_CLEAR_APIKEY	= "UPDATE {db_prefix}esam_api SET API_KEY = '' WHERE id_member = {member_id}";
    public $I_EVE_API_INSERT	= "INSERT INTO {db_prefix}esam_api (ID_MEMBER, USER_ID, API_KEY) VALUES ({member_id}, '{user_id}', '{api_key}')";

    // Rules
    public $Q_RULES			= "SELECT * FROM {db_prefix}esam_rules order by id_rule";
    public $D_RULE_DELETE	= "DELETE FROM {db_prefix}esam_rules WHERE ID_RULE = {id_rule}";
    public $I_RULE_INSERT	= "INSERT INTO {db_prefix}esam_rules (TYPE, ROLE, ROLE_ID, GROUP_ID, SKILL_ID, SKILL_LVL) VALUES ('{type}', '{role}', {role_id}, {group_id}, {skill_type_id}, {skill_lvl})";

    // Settings
    public $Q_MEMBER_SETTING	= "SELECT * FROM {db_prefix}esam_settings WHERE NAME = '{name}' AND ID_MEMBER = {member_id}";
    public $I_MEMBER_SETTING	= "INSERT INTO {db_prefix}esam_settings (name, value, id_member) VALUES ('{name}', '{value}', {member_id})";
    public $D_MEMBER_SETTING	= "DELETE FROM {db_prefix}esam_settings WHERE NAME = '{name}' AND ID_MEMBER = {member_id}";
    public $D_KILLALL_SETTING	= "DELETE FROM {db_prefix}esam_settings WHERE ID_MEMBER = {member_id}";
    public $Q_PORTRAIT_COUNT    = "SELECT count(*) count FROM {db_prefix}attachments WHERE ID_MEMBER = {member_id} and ID_MSG = 0 and ID_THUMB = 0";

    // Member Groups
    public $Q_MGROUP_LIST	= "select * from {db_prefix}membergroups where minPosts = -1 and id_group not in (-1, 0, 1, 3);";
    public $Q_MGROUP_INFO	= "SELECT * FROM {db_prefix}membergroups WHERE ID_GROUP = {id_group}";
    public $D_MGROUP_CLEAR	= "UPDATE {db_prefix}members SET additionalGroups = '' WHERE ID_MEMBER = {member_id}";
    public $U_MGROUP_ADD			= "
		UPDATE {db_prefix}members
		SET
			additionalGroups = IF(additionalGroups = '', '{id_group}', CONCAT(additionalGroups, ',{id_group}'))
		WHERE ID_MEMBER = {member_id}
			AND ID_GROUP != {id_group}
			AND NOT FIND_IN_SET({id_group}, additionalGroups)";

    // Member
    public $Q_MEMBER_INFO			= "SELECT * FROM {db_prefix}members WHERE ID_MEMBER = {member_id}";
    public $U_MEMBER_NAME			= "UPDATE {db_prefix}members set realName = '{real_name}' WHERE ID_MEMBER = {member_id}";
    public $U_MEMBERS_GROUPS	= "UPDATE {db_prefix}members set additionalGroups = '{member_groups}' WHERE ID_MEMBER = {member_id}";
	public $U_MEMBER_CUST_TITLE		= "UPDATE {db_prefix}members set userTitle = '{custom_title}' where ID_MEMBER = {member_id}";
    
    public $Q_CACHE_EXPIRE = "DELETE FROM {db_prefix}esam_cache where cacheUntil < now()";
    public $Q_CACHE_CLR = "DELETE FROM {db_prefix}esam_cache where url = '{url}'";
    public $Q_CACHE_SET = "UPDATE {db_prefix}esam_cache set result = {result} where url = '{url}'";
    public $Q_CACHE_INSERT = "INSERT INTO {db_prefix}esam_cache (url, result, cacheUntil) values ('{url}', '{result}', str_to_date('{cacheUntil}', '%Y-%m-%d %H:%i:%s'))";
    public $Q_CACHE_GET    = "SELECT result from {db_prefix}esam_cache where url = '{url}'";

    private $query = null;

    public function cacheGet($url)
    {
        $escaped_url = mysql_escape_string($url);

        $this->setQuery($this->Q_CACHE_EXPIRE);
        $this->executeQuery();

        $this->setQuery($this->Q_CACHE_GET);
        $this->setParameter("url", $escaped_url);
        return $this->getResultSetValue("result");
    }

    public function cacheSet($url, $result, $cacheUntil)
    {
        $escaped_url = mysql_escape_string($url);
        $escaped_result = mysql_escape_string($result);

        $this->setQuery($this->Q_CACHE_CLR);
        $this->setParameter("url", $escaped_url);
        $this->executeQuery();

        $this->setQuery($this->Q_CACHE_INSERT);
        $this->setParameter("url", $escaped_url);
        $this->setParameter("result", $escaped_result);
        $this->setParameter("cacheUntil", $cacheUntil);
        $this->executeQuery();
    }

    /**
     * Validates that the parameter is not an empty value, if it is,
     * calls fatal_error() with the given error message.
     *
     * @param <type> $parameter
     * @param <type> $errorMessage
     */
    public function validate($parameter, $errorMessage)
    {
        if (empty($parameter))
        {
            fatal_error($errorMessage, false);
        }
    }

    /**
     * Sets the executed query.
     *
     * @global <type> $db_prefix
     * @param <type> $_query
     */
    public function setQuery($_query)
    {
        global $db_prefix;

        $this->query = $_query;

        $this->setParameter("db_prefix", "{$db_prefix}");
    }

    /**
     * Sets the parameter name to the given value.
     *
     * NOTE: It is up to the caller to quote strings.
     *
     * @param <type> $name
     * @param <type> $value
     */
    public function setParameter($name, $value)
    {
        $this->validate($this->query, "Must call setQuery() first.");
        $this->validate($name, "name cannot be empty.");

        if (empty($value) || $value == null)
        {
            $value = "null";
        }

        $this->query = str_replace("{" . $name . "}", $value, $this->query);
    }

    public function executeQuery()
    {
    // print "<pre>" . $this->query . "</pre>";
        db_query($this->query, __FILE__, __LINE__);
    }

    /**
     * Executes the query and returns the result set as an array()
     *
     * @return <type>
     */
    public function getResultSet()
    {
        $request = db_query($this->query, __FILE__, __LINE__);
        $result = array();
        while($row = mysql_fetch_assoc($request))
        {
            $result[] = $row;
        }
        mysql_free_result($request);
        return $result;
    }

    /**
     * Executes the query and returns the the first value from
     * the given column.
     *
     * @return <type>
     */
    public function getResultSetValue($column)
    {
        $request = db_query($this->query, __FILE__, __LINE__);
        $row = mysql_fetch_assoc($request);
        mysql_free_result($request);
        return $row[$column];
    }
}
?>